﻿using Ayatta.Service.Extension;
using Dapper;
using System;
using System.Linq;
using System.Text;
using System.Data;
using Ayatta.Contract;
using System.Xml.Linq;
using Ayatta.Contract.Domain;
using System.Collections.Generic;
using Ayatta.Contract.Component;

namespace Ayatta.Service
{
    public class StoreService : ServiceBase, IStoreService
    {
        public bool CreateProductCategory(ProductCategory o)
        {
            var builder = new SqlBuilder();
            builder.Table(Database.Store.Table.ProductCategory)
                .Field("(Name)VALUES(@Name)");
            builder.AddParameters(new { o.Name });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public IList<ProductCategory> GetProductCategories(bool? status = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.ProductCategory);

            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status = status.Value });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<ProductCategory>(connection, template).ToList();
            }
        }

        public bool CreateProduct(Product o)
        {
            var result = false;
            var builderA = new SqlBuilder()
               .Table(Database.Store.Table.Product)
               .Field("([Name],[CategoryCode])VALUES(@Name,@CategoryCode)")
               .AddParameters(new { o.Name, o.CategoryCode });
            var templateA = builderA.AddTemplate(SqlTemplate.Insert);

            var extra = o.ProductExtra;
            var builderB = new SqlBuilder()
                  .Table(Database.Store.Table.ProductExtra)
                  .Field("([Id],[SeName])VALUES(@Id,@SeName)")
                  .AddParameters(new { extra.Id, extra.SeName });
            var templateB = builderB.AddTemplate(SqlTemplate.Insert);

            var builderC = new SqlBuilder()
                   .Table(Database.Store.Table.ProductVariant)
                   .Field("([ProductId],[VariantCode],[VariantPrice],[VariantQuantity],[CreatedBy],[CreatedOn])VALUES(@ProductId,@VariantCode,@VariantPrice,@VariantQuantity,@CreatedBy,@CreatedOn)");
            var templateC = builderC.AddTemplate(SqlTemplate.Insert);

            var builderD = new SqlBuilder()
                    .Table(Database.Store.Table.ProductAttribute)
                    .Field("([ProductId],[ProductCategoryCode],[AttributeNameCode],[AttributeValueCode],[CreatedBy],[CreatedOn])VALUES(@ProductId,@ProductCategoryCode,@AttributeNameCode,@AttributeValueCode,@CreatedBy,@CreatedOn)");
            var templateD = builderD.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var productId = InsertReturnLastId(o, connection, templateA, transaction);
                    if (productId > 0)
                    {
                        o.Id = productId;
                        ProcessProductAttribute(o);
                        //处理产品扩展信息

                        var a = Execute(connection, templateB, transaction) == 1;

                        //添加产品变体

                        var b = BatchInsert(connection, templateC.RawSql, o.ProductVariants, transaction) > 0;

                        //添加产品属性
                        var c = true;
                        if (o.ProductAttributes.Any())
                        {
                            c = BatchInsert(connection, templateD.RawSql, o.ProductAttributes, transaction) > 0;
                        }
                        result = a && b && c;
                        if (!result)
                        {
                            transaction.Rollback();
                        }
                        else
                        {
                            transaction.Commit();
                        }
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result = false;
                }
            }

            UpdateProductXmlAttribute(o);
            return result;
        }

        private static void ProcessProductAttribute(Product o)
        {
            o.ProductExtra.Id = o.Id;
            if (o.ProductAttributes.Any())
            {
                foreach (var attribute in o.ProductAttributes)
                {
                    attribute.ProductId = o.Id;
                }
            }
            foreach (var variant in o.ProductVariants)
            {
                variant.ProductId = o.Id;
            }
        }
        private void UpdateProductXmlAttribute(Product product)
        {
            List<ProductAttributeInfo> attributes;
            var template = GetProductAttributeNameValue(product.Id, product.CategoryCode, false);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                attributes = Query<ProductAttributeInfo>(connection, template).ToList();
            }
            if (attributes.Count > 0)
            {
                var xmlAttribute = new StringBuilder();
                xmlAttribute.Append("<attributes>");
                foreach (var o in attributes)
                {
                    xmlAttribute.AppendFormat(
                        "<attribute nameCode=\"{0}\" valueCode=\"{1}\" name=\"{2}\" value=\"{3}\"/>", o.NameCode,
                        o.ValueCode, o.Name, o.Value);
                }
                xmlAttribute.Append("</attributes>");
                product.XmlAttribute = xmlAttribute.ToString();
            }

            List<ProductAttributeInfo> saleAttributes;
            template = GetProductAttributeNameValue(product.Id, product.CategoryCode, true);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                saleAttributes = Query<ProductAttributeInfo>(connection, template).ToList();
            }
            if (saleAttributes.Count > 0)
            {
                var dic = saleAttributes.GroupBy(o => o.NameCode);
                var saleXmlAttribute = new StringBuilder();
                saleXmlAttribute.Append("<attributes>");
                foreach (var o in dic)
                {
                    var key = o.Key;
                    var firstValue = o.FirstOrDefault();
                    saleXmlAttribute.AppendFormat("<attribute code=\"{0}\" text=\"{1}\">", o.Key, firstValue.Name);
                    foreach (var v in saleAttributes.Where(x => x.ValueCode.StartsWith(key)))
                    {
                        saleXmlAttribute.AppendFormat("<value code=\"{0}\" text=\"{1}\"/>", v.ValueCode, v.Value);
                    }
                    saleXmlAttribute.Append("</attribute>");
                }
                saleXmlAttribute.Append("</attributes>");
                product.SaleXmlAttribute = saleXmlAttribute.ToString();
                template = InnerUpdateProductXmlAttribute(product);
                using (var connection = GetConnection(ConnectionName.Store))
                {
                    Execute(connection, template);
                }
            }
        }
        internal static SqlBuilder.Template InnerUpdateProductXmlAttribute(Product o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.Product)
                .Field("[XmlAttribute]=@XmlAttribute", new { o.XmlAttribute })
                .Field("[SaleXmlAttribute]=@SaleXmlAttribute", new { o.SaleXmlAttribute })
                .Where("[Id]=@Id", new { o.Id });
            return builder.AddTemplate(SqlTemplate.Update);
        }
        internal static SqlBuilder.Template GetProductAttributeNameValue(long productId, string productCategoryCode, bool isSaleAttribute)
        {
            var builder = new SqlBuilder()
                .Where(isSaleAttribute ? "[AttributeType]=2" : "[AttributeType]<>2")
                .Where("attribute.ProductId=@ProductId", new { productId });
            if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
            {
                var x = productCategoryCode.Length / 2;
                var y = new string[x];
                var length = 2;
                for (var i = 1; i <= x; i++)
                {
                    var temp = productCategoryCode.Substring(0, length);
                    y[i - 1] = temp;
                    length = (i + 1) * 2;
                }
                var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                builder.Where("(name.ProductCategoryCode IN(" + str + ") AND value.ProductCategoryCode  IN(" + str + "))");
            }
            else
            {
                builder.Where(
                    "(name.ProductCategoryCode =@ProductCategoryCode AND value.ProductCategoryCode=@ProductCategoryCode)",
                    new { productCategoryCode });
            }

            return
                builder.AddTemplate(
                    "select attribute.AttributeNameCode AS NameCode,attribute.AttributeValueCode AS ValueCode,name.AttributeName As Name,value.AttributeValueName AS Value from [ProductAttribute] attribute inner join dbo.ProductAttributeName name on attribute.AttributeNameCode=name.AttributeNameCode inner join  dbo.ProductAttributeValue value on attribute.AttributeValueCode=value.AttributeValueCode /**where**/");
        }

        public Product GetProduct(long id, bool inclExtra = false, bool inclVariants = false)
        {
            Product o;
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;", Database.Store.Table.Product);
            if (inclExtra)
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                Database.Store.Table.ProductExtra);
            }
            if (inclVariants)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [ProductId]=@Id;",
                                Database.Store.Table.ProductVariant);
            }
            var template = new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var multi = connection.QueryMultiple(template))
            {
                o = multi.Read<Product>().FirstOrDefault();
                if (o != null)
                {
                    if (inclExtra)
                    {
                        o.ProductExtra = multi.Read<ProductExtra>().FirstOrDefault();
                    }
                    if (inclVariants)
                    {
                        o.ProductVariants = multi.Read<ProductVariant>().ToList();
                    }
                }
            }
            return o;
        }


        public PagedList<Product> GetPagedProductList(int size = 20, int index = 1, string name = "")
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.Product)
                .OrderBy("id desc").AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name]=@Name", new { name });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedQuery<Product>( template);
            }
        }

        public bool UpdateProduct(Product o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.Product)
                .Field("[Name]=@Name", new { o.Name })
                .Where("[Id]=@Id AND [Version]=@Version", new { o.Id, o.Version });

            var template = builder.AddTemplate(SqlTemplate.Update);

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteProduct(long id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.Product)
                .Field("[Status]=@Status", new { Status = ProductItemStatus.Deleted })
                .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        #region ProductVariant
        //public ProductVariant GetProductVariant(long id, bool inclProduct = false, bool inclProductExtra = false)
        //{
        //    ProductVariant o;
        //    var template = productTemplate.GetProductVariant(id);
        //    using (var connection = GetConnection(ConnectionName.Store))
        //    {
        //        o = Query<ProductVariant>(connection, template).FirstOrDefault();
        //        if (o != null && inclProduct)
        //        {
        //            template = productTemplate.GetProduct(id);
        //            o.Product = Query<Product>(connection, template).FirstOrDefault();
        //            if (o.Product != null && inclProductExtra)
        //            {
        //                template = productTemplate.GetProductExtra(o.Product.Id);
        //                o.Product.ProductExtra = Query<ProductExtra>(connection, template).FirstOrDefault();
        //            }
        //        }
        //    }
        //    return o;
        //}

        public IList<ProductAttributeName> GetProductAttributeName(string productCategoryCode, string parentAttributeNameCode, int? id, bool? status)
        {
            IList<ProductAttributeName> result;
            var builder = new SqlBuilder().Field("*")
                   .Table(Database.Store.Table.ProductAttributeName)
                   .Where("[ParentAttributeNameCode]=@parentAttributeNameCode", new { parentAttributeNameCode });

            if (!string.IsNullOrEmpty(productCategoryCode))
            {
                if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
                {
                    var x = productCategoryCode.Length / 2;
                    var y = new string[x];
                    var length = 2;
                    for (var i = 1; i <= x; i++)
                    {
                        var temp = productCategoryCode.Substring(0, length);
                        y[i - 1] = temp;
                        length = (i + 1) * 2;
                    }
                    var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                    builder.Where("[ProductCategoryCode] IN(" + str + ")");
                }
                else
                {
                    builder.Where("[ProductCategoryCode]=@productCategoryCode", new { productCategoryCode });
                }
            }
            if (id.HasValue)
            {
                builder.Where("[Id]=@Id", new { id = id.Value });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status = status.Value });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = Query<ProductAttributeName>(connection, template).ToList();
            }
            return result;
        }

        public IList<ProductAttributeValue> GetProductAttributeValue(string productCategoryCode, bool byAttributeNameCode, string[] attributeNameCodeArray, int? id = null, bool? status = null)
        {
            IList<ProductAttributeValue> result;
            var builder = new SqlBuilder().Field("*")
                    .Table(Database.Store.Table.ProductAttributeValue);

            if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
            {
                var x = productCategoryCode.Length / 2;
                var y = new string[x];
                var length = 2;
                for (var i = 1; i <= x; i++)
                {
                    var temp = productCategoryCode.Substring(0, length);
                    y[i - 1] = temp;
                    length = (i + 1) * 2;
                }
                var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                builder.Where("[ProductCategoryCode] IN(" + str + ")");
            }
            else
            {
                builder.Where("[ProductCategoryCode]=@productCategoryCode", new { productCategoryCode });
            }

            if (byAttributeNameCode)
            {
                if (attributeNameCodeArray.Length > 0)
                {
                    var s = attributeNameCodeArray.Aggregate(string.Empty,
                                                             (current, o) => current + ("'" + o + "',"));
                    builder.Where(string.Format("[AttributeNameCode] IN ({0})", s.TrimEnd(',')));
                }
            }
            if (id.HasValue)
            {
                builder.Where("[Id]=@Id", new { id = id.Value });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status = status.Value });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = Query<ProductAttributeValue>(connection, template).ToList();
            }
            return result;
        }

        public bool CreateProductAttributeName(ProductAttributeName o)
        {
            var builder = new SqlBuilder()
                               .Table(Database.Store.Table.ProductAttributeName).Field(
                                   "([AttributeNameCode],[ParentAttributeNameCode],[ParentAttributeValueCode],[AttributeName],[AttributeNameDescription],[AttributeNameLevel],[AttributeType],[AttributeControlType],[IsMust],[ProductCategoryCode],[HasChild],[ChildAttributeNameCode],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@AttributeNameCode,@ParentAttributeNameCode,@ParentAttributeValueCode,@AttributeName,@AttributeNameDescription,@AttributeNameLevel,@AttributeType,@AttributeControlType,@IsMust,@ProductCategoryCode,@HasChild,@ChildAttributeNameCode,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                               .AddParameters(new { o.AttributeNameCode, o.ParentAttributeNameCode, o.ParentAttributeValueCode, o.AttributeName, o.AttributeNameDescription, o.AttributeNameLevel, o.AttributeType, o.AttributeControlType, o.IsMust, o.ProductCategoryCode, o.HasChild, o.ChildAttributeNameCode, o.DisplayOrder, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }

        }

        public bool CreateProductAttributeValue(ProductAttributeValue o)
        {
            var builder = new SqlBuilder()
                   .Table(Database.Store.Table.ProductAttributeValue)
                   .Field("([AttributeValueCode],[AttributeValueName],[AttributeValueDescription],[AttributeNameCode],[ProductCategoryCode],[PreSelected],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@AttributeValueCode,@AttributeValueName,@AttributeValueDescription,@AttributeNameCode,@ProductCategoryCode,@PreSelected,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                   .AddParameters(new { o.AttributeValueCode, o.AttributeValueName, o.AttributeValueDescription, o.AttributeNameCode, o.ProductCategoryCode, o.PreSelected, o.DisplayOrder, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }



        #endregion

        #region Discount

        public bool CreateDiscount(Discount o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Store.Table.Discount)
            .Field("([UserId],[Priority],[Title],[AssignedTo],[UsePercentage],[Percentage],[Amount],[StartTime],[EndTime],[Limitation],[LimitationValue],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@UserId,@Priority,@Title,@AssignedTo,@UsePercentage,@Percentage,@Amount,@StartTime,@EndTime,@Limitation,@LimitationValue,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.UserId, o.Priority, o.Title, o.AssignedTo, o.UsePercentage, o.Percentage, o.Amount, o.StartTime, o.EndTime, o.Limitation, o.LimitationValue, o.Extra, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public Discount GetDiscount(int id)
        {
            Discount o;
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE ID=@Id ORDER BY Priority DESC;", Database.Store.Table.Discount);
            sb.AppendFormat("SELECT * FROM {0} WHERE DiscountId=@Id;", Database.Store.Table.DiscountRequirement);
            var template = new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            using (var connection = GetConnection(ConnectionName.Store))
            using (var multi = connection.QueryMultiple(template))
            {
                o = multi.Read<Discount>().FirstOrDefault();
                if (o != null)
                {
                    o.DiscountRequirements = multi.Read<DiscountRequirement>().ToList();
                }
            }
            return o;
        }

        public PagedList<Discount> GetPagedDiscount(int size = 20, int index = 1, DiscountAssignedTo? assignedTo = null, bool? usePercentage = false, bool? status = false)
        {
            PagedList<Discount> o;
            var builder = new SqlBuilder()
                    .Field("*")
                    .Table(Database.Store.Table.Discount)
                    .OrderBy("id desc");
            if (assignedTo.HasValue)
            {
                builder.Where("[AssignedTo]=@AssignedTo", new { assignedTo });
            }
            if (usePercentage.HasValue)
            {
                builder.Where("[UsePercentage]=@UsePercentage", new { usePercentage });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            builder.AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedQuery<Discount>( template);
            }
        }

        public IList<DiscountRequirement> GetDiscountRequirement(int discountId)
        {
            IList<DiscountRequirement> result;
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = GetDiscountRequirement(connection, discountId);
            }
            return result;
        }

        //public IList<Discount> GetAvailableDiscount()
        //{
        //    IList<Discount> o = new List<Discount>();
        //    var builder = new SqlBuilder()
        //           .Field("*")
        //           .Table(Database.Store.Table.Discount)
        //           .Where("[Status]=1");

        //    var template = builder.AddTemplate(SqlTemplate.Select);
        //    using (var connection = GetConnection(ConnectionName.Store))
        //    {
        //        var temp = Query<Discount>(connection, template).ToList();
        //        foreach (var t in temp)
        //        {
        //            var startTime = t.StartTime.HasValue ? t.StartTime.Value : new DateTime(2010, 1, 1);
        //            var endTime = t.EndTime.HasValue ? t.EndTime.Value : DateTime.MaxValue;
        //            if (DateTime.Now >= startTime && DateTime.Now <= endTime)
        //            {
        //                o.Add(t);
        //            }
        //        }

        //        if (o.Any() && o.Count > 0)
        //        {
        //            foreach (var discount in o)
        //            {
        //                var requirements = GetDiscountRequirement(connection, discount.Id);
        //                discount.DiscountRequirements = requirements;
        //                switch (discount.AssignedTo)
        //                {
        //                    case DiscountAssignedTo.Category:
        //                        template = GetDiscountAssignedTo(DiscountAssignedTo.Category, discount.Id);
        //                        discount.AppliedToCategories = Query<string>(connection, template).ToList();
        //                        break;
        //                    case DiscountAssignedTo.Sku:
        //                        template = GetDiscountAssignedTo(DiscountAssignedTo.Sku, discount.Id);
        //                        discount.AppliedToSkus = Query<int>(connection, template).ToList();
        //                        break;
        //                }
        //            }
        //        }
        //    }
        //    return o;
        //}
        internal static SqlBuilder.Template GetDiscountAssignedTo(DiscountAssignedTo assignedTo, int discountId)
        {
            var builder = new SqlBuilder();
            switch (assignedTo)
            {
                case DiscountAssignedTo.Category:
                    builder.Field("[CategoryCode]").Table(
                        Database.Store.Table.DiscountProductCategoryMapping);
                    break;
                case DiscountAssignedTo.Sku:
                    builder.Field("[SkuId]").Table(
                        Database.Store.Table.DiscountItemMap);
                    break;
            }
            builder.Where("[Status]=1 AND [DiscountId]=@DiscountId", new { discountId });
            return builder.AddTemplate(SqlTemplate.Select);
        }

        public bool ValidateDiscount(int discountId, int limitationTimes, int? userId)
        {
            var builder = new SqlBuilder()
                .Field("Count(0) as Count")
                .Table(Database.Store.Table.DiscountUsageHistory)
                .Where("[DiscountId]=@DiscountId", new { discountId });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[BuyerId]=@UserId", new { userId });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<int>(connection, template).First() <= limitationTimes;
            }
        }

        public IList<Discount> GetDiscounts(int userId)
        {
            var tpl = string.Format("SELECT * FROM {0} WHERE [UserId]=@UserId AND [Status]=1 ORDER BY Priority DESC;SELECT * FROM {1} WHERE [UserId]=@UserId AND [Status]=1", Database.Store.Table.Discount, Database.Store.Table.DiscountRequirement);

            var template = new SqlBuilder().AddTemplate(tpl, new { userId });

            using (var connection = GetConnection(ConnectionName.Store))
            using (var multi = connection.QueryMultiple(template))
            {
                return multi.Map<Discount, DiscountRequirement, int>(a => a.Id, b => b.DiscountId, (a, b) => { a.DiscountRequirements = b; }).ToList();
            }
        }

        public bool CreateDiscountRequirement(DiscountRequirement o)
        {
            var builder = new SqlBuilder()
             .Table(Database.Store.Table.DiscountRequirement)
             .Field("([DiscountId],[RequirementRule],[SpentAmount],[ShippingAreaIds],[UserRoleIds],[SkuIds],[ItemIds],[CategoryIds][Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@DiscountId,@RequirementRule,@SpentAmount,@ShippingAreaIds,@UserRoleIds,@SkuIds,@ItemIds,@CategoryIds,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
             .AddParameters(new { o.DiscountId, o.RequirementRule, o.SpentAmount, o.ShippingAreaIds, o.UserRoleIds, o.SkuIds, o.ItemIds, o.CategoryIds, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        private static IList<DiscountRequirement> GetDiscountRequirement(IDbConnection connection, int discountId)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.DiscountRequirement)
                .Where("[DiscountId]=@DiscountId", new { discountId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            return Query<DiscountRequirement>(connection, template).ToList();
        }

        #endregion

        #region ProductAdvisory
        public bool CreateProductAdvisory(ProductAdvisory o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Store.Table.ProductAdvisory)
            .Field("([Category],[ProductId],[UserId],[NickName],[Question],[Answer],[Answerer],[AnswerTime],[Satisfaction],[DisSatisfaction],[Status],[CreatedBy],[CreatedOn])VALUES(@Category,@ProductId,@UserId,@NickName,@Question,@Answer,@Answerer,@AnswerTime,@Satisfaction,@DisSatisfaction,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Category, o.ProductId, o.UserId, o.NickName, o.Question, o.Answer, o.Answerer, o.AnswerTime, o.Satisfaction, o.DisSatisfaction, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateProductAdvisory(ProductAdvisory o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.ProductAdvisory)
                .Field("[Category]=@Category", new { o.Category })
                .Field("[ProductId]=@ProductId", new { o.ProductId })
                .Field("[UserId]=@UserId", new { o.UserId })
                .Field("[NickName]=@NickName", new { o.NickName })
                .Field("[Question]=@Question", new { o.Question })
                .Field("[Answer]=@Answer", new { o.Answer })
                .Field("[Answerer]=@Answerer", new { o.Answerer })
                .Field("[AnswerTime]=@AnswerTime", new { o.AnswerTime })
                .Field("[Satisfaction]=@Satisfaction", new { o.Satisfaction })
                .Field("[DisSatisfaction]=@DisSatisfaction", new { o.DisSatisfaction })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteProductAdvisory(long id)
        {
            var builder = new SqlBuilder()
              .Table(Database.Store.Table.ProductAdvisory)
              .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public ProductAdvisory GetProductAdvisory(long id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.ProductAdvisory)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<ProductAdvisory>(connection, template).FirstOrDefault();
            }
        }

        public PagedList<ProductAdvisory> GetPagedProductAdvisory(int index = 1, int size = 20, long? productId = null, int? userId = null, string createdBy = null, DateTime? createdOn = null, DateTime? answerTime = null, ProductAdvisoryCategory? category = null, ProductAdvisoryStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.ProductAdvisory)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (productId.HasValue && productId.Value > 0)
            {
                builder.Where("[ProductId=@ProductId", new { productId });
            }
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(createdBy))
            {
                builder.Where("[CreatedBy=@CreatedBy", new { createdBy });
            }
            if (createdOn.HasValue)
            {
                var createdOnBegin = new DateTime(createdOn.Value.Year, createdOn.Value.Month, createdOn.Value.Day);
                var createdOnEnd = new DateTime(createdOn.Value.Year, createdOn.Value.Month, createdOn.Value.Day).AddDays(1);
                builder.Where("[CreatedOn>=@CreatedOnBegin AND [CreatedOn<@CreatedOnEnd", new { createdOnBegin, createdOnEnd });
            }
            if (answerTime.HasValue)
            {
                var answerTimeBegin = new DateTime(answerTime.Value.Year, answerTime.Value.Month, answerTime.Value.Day);
                var answerTimeEnd = new DateTime(answerTime.Value.Year, answerTime.Value.Month, answerTime.Value.Day).AddDays(1);
                builder.Where("[AnswerTime>=@AnswerTimeBegin AND [AnswerTime<@AnswerTimeEnd", new { answerTimeBegin, answerTimeEnd });
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category=@Category", new { category });
            }
            if (status.HasValue && status.Value > 0)
            {
                builder.Where("[Status=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedQuery<ProductAdvisory>(template);
            }
        }
        #endregion

        #region ProductReview
        public bool CreateProductReview(ProductReview o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.ProductReview);
            //.Field("([ProductId],[Satisfaction],[Title],[Advantage],[Shortage],[Experience],[Useful],[UserId],[NickName],[Status],[CreatedBy],[CreatedOn])VALUES(@ProductId,@Satisfaction,@Title,@Advantage,@Shortage,@Experience,@Useful,@UserId,@NickName,@Status,@CreatedBy,@CreatedOn)")
            // .AddParameters(new { o.ProductId, o.Satisfaction, o.Title, o.Advantage, o.Shortage, o.Experience, o.Useful, o.UserId, o.NickName, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateProductReview(ProductReview o)
        {
            var builder = new SqlBuilder()
                    .Table(Database.Store.Table.ProductReview)
                    .Field("[ProductId]=@ProductId", new { o.ProductId })
                    .Field("[Satisfaction]=@Satisfaction", new { o.Satisfaction })
                // .Field("[Title]=@Title", new { o.Title })
                //.Field("[Advantage]=@Advantage", new { o.Advantage })
                //.Field("[Shortage]=@Shortage", new { o.Shortage })
                //.Field("[Experience]=@Experience", new { o.Experience })
                //.Field("[Useful]=@Useful", new { o.Useful })
                //.Field("[UserId]=@UserId", new { o.UserId })
                //.Field("[NickName]=@NickName", new { o.NickName })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteProductReview(long id)
        {
            var builder = new SqlBuilder()
               .Table(Database.Store.Table.ProductReview)
               .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public ProductReview GetProductReview(long id)
        {
            var builder = new SqlBuilder()
              .Field("*")
              .Table(Database.Store.Table.ProductReview)
              .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<ProductReview>(connection, template).FirstOrDefault();
            }
        }

        public PagedList<ProductReview> GetPagedProductReview(int index = 1, int size = 20, long? productId = null,
                                                          int? userId = null, string createdBy = null,
                                                          DateTime? createdOn = null,
                                                          ProductReviewStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.ProductReview)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedQuery<ProductReview>(template);
            }
        }

        #endregion
        #region ProductItem
        public ProductItem GetProductItem(int id, int? userId = null)
        {
            var sql = "SELECT * FROM {0} WHERE Id=@Id;SELECT * FROM {1} WHERE ItemId=@Id;";
            if (userId.HasValue && userId.Value > 0)
            {
                sql = "SELECT * FROM {0} WHERE Id=@Id AND UserId=" + userId.Value + ";SELECT * FROM {1} WHERE ItemId=@Id AND UserId=" + userId.Value + ";";
            }
            var tpl = string.Format(sql, Database.Store.Table.ProductItem, Database.Store.Table.ProductItemSku);

            var template = new SqlBuilder().AddTemplate(tpl, new { id });
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.QueryMultiple(template).Map<ProductItem, Sku, int>(a => a.Id, b => b.ItemId, (a, b) => { a.Skus = b.ToList(); }).FirstOrDefault();
            }
        }

        public ProductItem GetProductItemName(int id)
        {
            var builder = new SqlBuilder()
                 .Field("[Id],[OuterId],[Name],[SubName]")
                 .Table(Database.Store.Table.ProductItem)
                 .Where("[Id] = @Id", new { id });
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Query<ProductItem>(template).FirstOrDefault();
            }
        }

        public ProductItem GetProductItemPriceQuantity(int id, int? userId = null)
        {
            var sql = "SELECT [Id],[OuterId],[Name],[Price],[Quantity],[Status]  FROM {0} WHERE Id=@Id;SELECT [Id],[ItemId],[OuterId],[Price],[Quantity],[PropertiesName] FROM {1} WHERE ItemId=@Id;";
            if (userId.HasValue && userId.Value > 0)
            {
                sql = "SELECT [Id],[OuterId],[Name],[Price],[Quantity],[Status]  FROM {0} WHERE Id=@Id AND UserId=" + userId.Value + ";SELECT [Id],[ItemId],[OuterId],[Price],[Quantity],[PropertiesName] FROM {1} WHERE ItemId=@Id AND UserId=" + userId.Value + ";";
            }
            var tpl = string.Format(sql, Database.Store.Table.ProductItem, Database.Store.Table.ProductItemSku);

            var template = new SqlBuilder().AddTemplate(tpl, new { id });
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.QueryMultiple(template).Map<ProductItem, Sku, int>(a => a.Id, b => b.ItemId, (a, b) => { a.Skus = b.ToList(); }).FirstOrDefault();
            }
        }

        public int CreateProductItem(ProductItem o)
        {
            var xml = string.Empty;
            var xElement = new XElement("xml");
            try
            {
                xElement.Add(new XElement("item",
                                          new XAttribute("Id", o.Id),
                                          new XAttribute("UserId", o.UserId),
                                          new XAttribute("ProductId", o.ProductId),
                                          new XAttribute("CategoryId", o.CategoryId),
                                          new XAttribute("CategoryRootId", o.CategoryRootId),
                                          new XAttribute("CategoryMiddleId", o.CategoryMiddleId),
                                          new XAttribute("OuterId", o.OuterId),
                                          new XAttribute("Name", o.Name),
                                          new XAttribute("SubName", o.SubName),
                                          new XAttribute("Price", o.Price.ToString("N")),
                                          new XAttribute("Quantity", o.Quantity),
                                          new XAttribute("RewardRate", o.RewardRate),
                                          new XAttribute("Properties", o.Properties),
                                          new XAttribute("PropertiesName", o.PropertiesName),
                                          new XAttribute("PropertiesAlias", o.PropertiesAlias),
                                          new XAttribute("InputIds", o.InputIds),
                                          new XAttribute("InputStrs", o.InputStrs),
                                          new XAttribute("Picture", o.Picture),
                                          new XAttribute("ItemImgs", o.ItemImgs),
                                          new XAttribute("PropImgs", o.PropImgs),
                                          new XAttribute("Weight", o.Weight.ToString("N")),
                                          new XAttribute("Size", o.Size),
                                          new XElement("Description", new XCData(o.Description)),
                                          new XAttribute("IsVirtual", o.IsVirtual),
                                          new XAttribute("SubStock", o.SubStock),
                                          new XAttribute("AutoFill", o.AutoFill),
                                          new XAttribute("IsTiming", o.IsTiming),
                                          new XAttribute("ShowCase", o.ShowCase),
                                          new XAttribute("OnlineTime", o.OnlineTime),
                                          new XAttribute("OfflineTime", o.OfflineTime),
                                          new XAttribute("Sale", o.Sale),
                                          new XAttribute("Status", (byte)o.Status),
                                          new XAttribute("CreatedBy", o.CreatedBy)
                                 ));

                xElement.Add(new XElement("skus", o.Skus.Select(x => new XElement("sku",
                                                                                  new XAttribute("Id", x.Id),
                                                                                  new XAttribute("ItemId", x.ItemId),
                                                                                  new XAttribute("UserId", x.UserId),
                                                                                  new XAttribute("ProductId", x.ProductId),
                                                                                  new XAttribute("CategoryId", x.CategoryId),
                                                                                  new XAttribute("CategoryRootId", o.CategoryRootId),
                                                                                  new XAttribute("CategoryMiddleId", o.CategoryMiddleId),
                                                                                  new XAttribute("OuterId", x.OuterId),
                                                                                  new XAttribute("Properties", x.Properties),
                                                                                  new XAttribute("PropertiesName", x.PropertiesName),
                                                                                  new XAttribute("Quantity", x.Quantity),
                                                                                  new XAttribute("Price", x.Price.ToString("N")),
                                                                                  new XAttribute("Status", x.Status),
                                                                                  new XAttribute("SoldAmount", x.SoldAmount),
                                                                                  new XAttribute("CreatedOn", x.CreatedOn),
                                                                                  new XAttribute("ModifiedOn", x.ModifiedOn)
                                                                         ))));

                xml = xElement.ToString();
                var param = new DynamicParameters();
                param.Add("@xml", xml, DbType.Xml);
                param.Add("@itemId", 0, direction: ParameterDirection.Output);

                using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
                {
                    connection.Execute("[Proc.Product.Item.Create]", param, commandType: CommandType.StoredProcedure);
                    return param.Get<int>("@itemId");
                }
            }
            catch (Exception e)
            {
                System.IO.File.WriteAllText("E:/Ayatta/WebSite/Web.Wcf.Host/a.txt", xml);
                return 0;
                // throw;
            }
        }

        public PagedList<ProductItem> GetPagedProductItems(int index = 1, int size = 20,byte param=0, int? userId = null, int? id = null, string name = null, string outerId = null, int? categoryRootId = null, decimal? priceFrom = null, decimal? priceTo = null, int? saleFrom = null, int? saleTo = null, IDictionary<string, string> orderBy = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.ProductItem)
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }
            if (param >=0 && param<=2)
            {
                builder.Where("[Status]=" + param);
            }
            else if (param == 3)//定时上架
            {
                builder.Where("[IsTiming]=1");
            }
            if (id.HasValue && id.Value > 0)
            {
                builder.Where("[Id]=@Id", new {id});
            }
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (!string.IsNullOrEmpty(outerId))
            {
                builder.Where("[OuterId]=@OuterId", new {outerId});
            }
            if (categoryRootId.HasValue && categoryRootId.Value > 0)
            {
                builder.Where("[CategoryRootId]=@CategoryRootId", new {categoryRootId});
            }
            if (priceFrom.HasValue && priceFrom.Value > 0)
            {
                builder.Where("[Price]>=@PriceFrom", new {priceFrom});
            }
            if (priceTo.HasValue && priceTo.Value > 0)
            {
                builder.Where("[Price]<=@PriceTo", new {priceTo});
            }
            if (saleFrom.HasValue && saleFrom.Value > 0)
            {
                builder.Where("[Sale]>=@SaleFrom", new {saleFrom});
            }
            if (saleTo.HasValue && saleTo.Value > 0)
            {
                builder.Where("[Sale]<=@SaleTo", new {saleTo});
            }
            if (orderBy != null&&orderBy.Count>0)
            {
                var sb = new StringBuilder();
                foreach (var o in orderBy)
                {
                    sb.AppendFormat("[{0}] {1},", o.Key, o.Value);
                }
                builder.OrderBy(sb.ToString().TrimEnd(','));
            }
            else
            {
                builder.OrderBy("[CreatedOn] DESC");
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedQuery<ProductItem>(template);
            }
        }

        public bool UpdateProductItemName(int id, int userId, string name, string subName)
        {
            var builder = new SqlBuilder()
                .Field("[Name]=@Name", new { name })
                .Field("[SubName]=@SubName", new { subName })
                .Table(Database.Store.Table.ProductItem)
                .Where("[Id] = @Id AND [UserId]=@UserId", new { id, userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Execute(template) == 1;
            }
        }

        public bool UpdateProductItemPrice(int id, int userId, decimal unitPrice, IDictionary<int, decimal> prices)
        {
            var xElement = new XElement("xml");
            xElement.Add(new XElement("item",
                                      new XAttribute("Id", id),
                                      new XAttribute("UserId", userId),
                                      new XAttribute("Price", unitPrice.ToString("N"))
                             ));
            xElement.Add(new XElement("skus", prices.Select(x => new XElement("sku",
                                                                              new XAttribute("Id", x.Key),
                                                                              new XAttribute("UserId", userId),
                                                                              new XAttribute("Price", x.Value.ToString("N"))
                                                                     ))));
            var xml = xElement.ToString();
            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);
            param.Add("@status", false, direction: ParameterDirection.Output);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                connection.Execute("[Proc.Product.Item.Price.Update]", param, commandType: CommandType.StoredProcedure);
                return param.Get<bool>("@status");
            }
        }

        public bool UpdateProductItemQuantity(int id, int userId, int quantity, IDictionary<int, int> quantities)
        {
            var xElement = new XElement("xml");
            xElement.Add(new XElement("item",
                                      new XAttribute("Id", id),
                                      new XAttribute("UserId", userId),
                                      new XAttribute("Quantity", quantity)
                             ));
            xElement.Add(new XElement("skus", quantities.Select(x => new XElement("sku",
                                                                                  new XAttribute("Id", x.Key),
                                                                                  new XAttribute("UserId", userId),
                                                                                  new XAttribute("Quantity", x.Value)
                                                                         ))));
            var xml = xElement.ToString();
            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);
            param.Add("@status", false, direction: ParameterDirection.Output);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                connection.Execute("[Proc.Product.Item.Quantity.Update]", param, commandType: CommandType.StoredProcedure);
                return param.Get<bool>("@status");
            }
        }

        #endregion

        public Sku GetSku(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.ProductItemSku)
                .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Select);

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<Sku>(connection, template).FirstOrDefault();
            }

        }
        #region Implementation of IService

        public int GetCachedSqlCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }
        public List<Tuple<string, string, int>> GetCachedSql()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }
        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }
        #endregion
    }
}